package com.css.fxfzaqbz.modules.data.repository.impl;

import com.css.fxfzaqbz.constants.YNEnum;
import com.css.fxfzaqbz.modules.data.entity.RiskEnterpriseData;
import com.css.fxfzaqbz.modules.data.queryParams.RiskEnterpriseDataQueryParams;
import com.css.fxfzaqbz.modules.data.repository.RiskEnterpriseDataRepository;
import com.css.fxfzaqbz.util.PlatformObjectUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class RiskEnterpriseDataRepositoryImpl implements RiskEnterpriseDataRepository {
    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

//    @Override
//    public RiskEnterpriseDataVO updateElevation(RiskEnterpriseDataVO riskEnterpriseDataVO) {
//
//        String sql = "update sys_elevation set elevation_number = :elevationNumber, elevation_band = :elevationBand, elevation_nodatavalue = :elevationNodatavalue, elevation_min = :elevationMin, elevation_max = :elevationMax  where elevation_id = '"+elevationVO.getElevationId()+"'" ;
//
//        SqlParameterSource source = new BeanPropertySqlParameterSource(elevationVO);
//        //拿到新插入的主键值（i）
//        KeyHolder keyHolder = new GeneratedKeyHolder();
//        namedParameterJdbcTemplate.update(sql,source,keyHolder);
//        return elevationVO;
//    }

    @Override
    public RiskEnterpriseData saveRiskEnterpriseData(RiskEnterpriseData riskEnterpriseData) {


        String sql = "insert into sys_riskenterprisedata values(:redId,:redNumber,:redLongitude,:redLatitude,:redName,:redAddress,:redType,:redScale,:redRiskgrade,:redStatus,:redUpdatetime,:redCreatetime)";

        SqlParameterSource source = new BeanPropertySqlParameterSource(riskEnterpriseData);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return riskEnterpriseData;



    }

    @Override
    public Page<RiskEnterpriseData> getAllRiskEnterpriseDataParams(RiskEnterpriseDataQueryParams queryParams) {
        StringBuilder sql = new StringBuilder(" select  r.* from sys_riskenterprisedata r ");

        StringBuilder orderSql = new StringBuilder(" order by r.red_id");

        StringBuilder whereSql = new StringBuilder(" where r.red_status = '" + YNEnum.Y.toString() + "'");

//        if (!PlatformObjectUtils.isEmpty(queryParams.getRedName()) ){
//            whereSql.append(" and r.red_name like '%" + queryParams.getRedName() + "%'");
//        }
//
//        if (!PlatformObjectUtils.isEmpty(queryParams.getRedAddress()) ){
//           whereSql.append(" and r.red_address like '%" + queryParams.getRedAddress() + "%'");
//        }

        String wholeSql = sql.append(whereSql).append(orderSql).toString();
        List<RiskEnterpriseData> list = jdbcTemplate.query(wholeSql, new BeanPropertyRowMapper<>(RiskEnterpriseData.class));
        Pageable pageable = PageRequest.of(queryParams.getCurPage() - 1, queryParams.getPageSize());

        Page<RiskEnterpriseData> riskEnterpriseDatas = new PageImpl<>(list, pageable, PlatformObjectUtils.isEmpty(list) ? 0 : list.size());

        return riskEnterpriseDatas;
    }

    @Override
    public RiskEnterpriseData getRiskEnterpriseDataById(String id) {
        String sql = "select * from sys_riskenterprisedata where red_id = '"+id+"'";
        RiskEnterpriseData riskEnterpriseData = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(RiskEnterpriseData.class));
        return riskEnterpriseData;
    }

    @Override
    public void deleteRiskEnterpriseDatasById(List<String> ids, Timestamp timestamp) {
        String sql = "update sys_riskenterprisedata set red_status = '" + YNEnum.N + "' , red_updatetime = '"+timestamp+"'" + " where red_id in (:ids)";
        Map map = new HashMap();
        map.put("ids", ids);
        namedParameterJdbcTemplate.update(sql, map);
    }

    @Override
    public List<RiskEnterpriseData> getAll() {
        return jdbcTemplate.query("select * from sys_riskenterprisedata",new BeanPropertyRowMapper<>(RiskEnterpriseData.class));
    }

    @Override
    public void importExcel(List<RiskEnterpriseData> riskEnterpriseData) {
        jdbcTemplate.batchUpdate("insert into sys_riskenterprisedata (red_id,red_number,red_longitude,red_latitude,red_name,red_address,red_type,red_scale,red_riskgrade,red_status,red_createtime)values(?,?,?,?,?,?,?,?,?,?,?)", new BatchPreparedStatementSetter() {
            @Override
            public int getBatchSize() {
                return riskEnterpriseData.size();
            }
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1,riskEnterpriseData.get(i).getRedId());
                ps.setString(2,riskEnterpriseData.get(i).getRedNumber());
                ps.setFloat(3, riskEnterpriseData.get(i).getRedLongitude());
                ps.setFloat(4,riskEnterpriseData.get(i).getRedLatitude());
                ps.setString(5,riskEnterpriseData.get(i).getRedName());
                ps.setString(6,riskEnterpriseData.get(i).getRedAddress());
                ps.setString(7,riskEnterpriseData.get(i).getRedType());
                ps.setString(8,riskEnterpriseData.get(i).getRedScale());
                ps.setString(9,riskEnterpriseData.get(i).getRedRiskgrade());
                ps.setString(10,riskEnterpriseData.get(i).getRedStatus());
                java.sql.Date stimeDate = new java.sql.Date(riskEnterpriseData.get(i).getRedCreatetime().getTime()) ;
                ps.setDate(11,stimeDate);
            }
        });
    }

}
